package Logic;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.LinkedList;

import javax.swing.JOptionPane;

public class DatabaseReader {

	String url;
	String user;
	String password;

	Connection connection;
	Statement statement;
	PreparedStatement prepStatement;
	ResultSet resultSet;
	Boolean dbConnected = false;
	String query;

	LinkedList<String[]> availableFlights;
	LinkedList<String> tripIDs;

	public DatabaseReader() {
//		url = "jdbc:postgresql://localhost:5432/flightbooker";
//		user = "postgres";
//		password = "admin";

		connection = null;
		statement = null;
		resultSet = null;
//
//		try {
//			connection = DriverManager.getConnection(url, user, password);
//			statement = connection.createStatement();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
	}

	/**
	 * Sets the informations needed to connect the program to the database-server.
	 */
	public String setDataBaseCredentials(String host, int port, String dbName,
			String user, String pwd) {
		url = "jdbc:postgresql://" + host + ":" + port + "/" + dbName;
		this.user = user;
		password = pwd;
		try {
			connection = DriverManager.getConnection(url, this.user, password);
			statement = connection.createStatement();
			dbConnected = true;
			return "Database connected!";
		} catch (SQLException e) {
			return "Connection failed: " + e.getMessage();
		}

	}

	public boolean isDatabaseConnected() {
		return dbConnected;
	}

	/**
	 * Reads the searched trips from the database and stores them inside a LinkedList, with arrays
	 * containing the single columns (by using the search-parameters passed from the user).
	 */
	public LinkedList<String[]> readFlights(String depLoc, String arrLoc,
			String depDat, String maxPrice/* , String flightClass */) {
		availableFlights = new LinkedList<String[]>();
		String[] singleRow = new String[6];
		tripIDs = new LinkedList<String>();

		query = "SELECT * FROM trip";
		checkDepLoc(translateCityToID(depLoc), translateCityToID(arrLoc),
				depDat, formatCommaInPrice(maxPrice));

		if (query.length() > 18)
			query += ";";

		try {
			resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				singleRow = new String[6];

				singleRow[0] = formatTime(resultSet.getString("departuretime"));
				singleRow[1] = translateIDToCity(resultSet
						.getString("departurelocation"));
				singleRow[2] = formatTime(resultSet.getString("arrivaltime"));
				singleRow[3] = translateIDToCity(resultSet
						.getString("arrivallocation"));
				singleRow[4] = resultSet.getString("flights");
				singleRow[5] = formatPrice(resultSet.getString("price"))
						+ " €";

				availableFlights.add(singleRow);
				tripIDs.add(resultSet.getString("tripid")); // TO TEST
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return availableFlights;
	}

	/**
	 * Adds the needed departure location to the query.
	 */
	private void checkDepLoc(String depLoc, String arrLoc, String depDat,
			String maxPrice) {
		if (checkParamCorrect(depLoc) == false)
			checkArrLoc(true, arrLoc, depDat, maxPrice);
		else {
			query = query + " WHERE departurelocation='" + depLoc + "'";
			checkArrLoc(false, arrLoc, depDat, maxPrice);
		}
	}

	/**
	 * Adds the needed arrival location to the query.
	 */
	private void checkArrLoc(boolean firstInQuery, String arrLoc,
			String depDat, String maxPrice) {
		if (checkParamCorrect(arrLoc) == false)
			checkDepDat(firstInQuery, depDat, maxPrice);
		else {
			if (firstInQuery)
				query = query + " WHERE arrivallocation='" + arrLoc + "'";
			else
				query = query + " AND arrivallocation='" + arrLoc + "'";

			checkDepDat(false, depDat, maxPrice);
		}
	}

	/**
	 * Adds the needed departure date to the query.
	 */
	private void checkDepDat(boolean firstInQuery, String depDat,
			String maxPrice) {
		if (checkParamCorrect(depDat) == false)
			checkMaxPrice(firstInQuery, maxPrice);
		else {
			String substr1 = depDat.substring(8, 10);
			Integer substr2Int = Integer.parseInt(substr1) + 1;
			String dayAfterDat;

			if (substr2Int.toString().length() == 2)
				dayAfterDat = depDat.replace(substr1 + " ",
						substr2Int.toString() + " ");
			else
				dayAfterDat = depDat.replace(substr1 + " ",
						0 + substr2Int.toString() + " ");

			if (firstInQuery)
				query = query + " WHERE departuretime >= '" + depDat
						+ "' AND departuretime < '" + dayAfterDat + "'";
			else
				query = query + " AND departuretime >= '" + depDat
						+ "' AND departuretime < '" + dayAfterDat + "'";

			checkMaxPrice(false, maxPrice);
		}
	}

	/**
	 * Adds the needed maximal price to the query.
	 */
	private void checkMaxPrice(boolean firstInQuery, String maxPrice) {
		if (checkParamCorrect(maxPrice)) {
			if (firstInQuery)
				query = query + " WHERE price <= " + maxPrice;
			else
				query = query + " AND price <= " + maxPrice;
		}
	}

	private boolean checkParamCorrect(String parameter) {
		if (parameter.length() > 0)
			return true;
		else
			return false;
	}

	/**
	 * Translates the airport-id into the city where the airport is located.
	 */
	private String translateIDToCity(String id) {
		String cityName = "";
		String locQuery = "SELECT * FROM airport WHERE airportid='" + id + "'";
		ResultSet resultSetLoc;
		Statement statementLoc;

		try {
			statementLoc = connection.createStatement();
			resultSetLoc = statementLoc.executeQuery(locQuery);

			while (resultSetLoc.next()) {
				cityName = resultSetLoc.getString("city");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return cityName;
	}

	/**
	 * Translates the city into the id of the airport located there.
	 */
	private String translateCityToID(String cityName) {
		String id = "";
		String locQuery = "SELECT * FROM airport WHERE city='" + cityName + "'";
		ResultSet resultSetLoc;
		Statement statementLoc;

		try {
			statementLoc = connection.createStatement();
			resultSetLoc = statementLoc.executeQuery(locQuery);

			while (resultSetLoc.next()) {
				id = resultSetLoc.getString("airportid");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return id;
	}

	public Connection getConnection() {
		return connection;
	}

	private String formatTime(String unformattedTime) {
		String formattedTime = unformattedTime.substring(0, 16);

		return formattedTime;
	}

	private String formatPrice(String unformattedPrice) {
		int posOfPoint = unformattedPrice.indexOf('.');
		String formattedPrice = unformattedPrice;

		if (posOfPoint > 0) {
			int lenAfterPoint = unformattedPrice.length() - posOfPoint - 1;

			if (lenAfterPoint > 2)
				formattedPrice = unformattedPrice.substring(0, posOfPoint + 3);

			if (lenAfterPoint == 1)
				formattedPrice = unformattedPrice + "0";
		}

		return formattedPrice;
	}

	private String formatCommaInPrice(String unformattedPrice) {
		String formattedPrice = unformattedPrice.replace(',', '.');

		return formattedPrice;
	}

	/**
	 * Assembles all the flights contained inside a specific trip.
	 */
	private LinkedList<String> readFlightsFromTrip(int tripIndex)
	{
		String tripID = tripIDs.get(tripIndex);
		String flightQuery = "SELECT * FROM flighttrip WHERE trip='" + tripID
				+ "'";
		ResultSet resultSetFlight;
		Statement statementFlight;

		LinkedList<String> flightsOfTrip = new LinkedList<String>();

		try {
			statementFlight = connection.createStatement();
			resultSetFlight = statementFlight.executeQuery(flightQuery);

			while (resultSetFlight.next()) {
				flightsOfTrip.add(resultSetFlight.getString("flight"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return flightsOfTrip;
	}
	
	/**
	 * Assembles all the flight-informations of a trip contained inside a specific trip.
	 */
	public LinkedList<String[]> readFlightsInformations(int tripIndex)
	{
		LinkedList<String> flightsOfTrip = readFlightsFromTrip(tripIndex);
		LinkedList<String[]> flightInformations = new LinkedList<String[]>();
		String[] singleRow = new String[6];

		ResultSet resultSetFlightInfo;
		Statement statementFlightInfo;
		String flightInfoQuery;

		try {
			statementFlightInfo = connection.createStatement();

			for (String flightID : flightsOfTrip) {
				flightInfoQuery = "SELECT * FROM flight WHERE flightnumber='"
						+ flightID + "'";
				resultSetFlightInfo = statementFlightInfo
						.executeQuery(flightInfoQuery);

				while (resultSetFlightInfo.next()) {
					singleRow = new String[6];

					singleRow[0] = resultSetFlightInfo
							.getString("flightnumber");
					singleRow[1] = formatTime(resultSetFlightInfo
							.getString("departuretime"));
					singleRow[2] = translateIDToCity(resultSetFlightInfo
							.getString("departurelocation"));
					singleRow[3] = formatTime(resultSetFlightInfo
							.getString("arrivaltime"));
					singleRow[4] = translateIDToCity(resultSetFlightInfo
							.getString("arrivallocation"));
					singleRow[5] = formatPrice(resultSetFlightInfo
							.getString("price")) + " €";

					flightInformations.add(singleRow);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return flightInformations;
	}

	public boolean logOn(String userID, String pwd) {

		try {
			prepStatement = connection
					.prepareStatement("SELECT * FROM \"users\" WHERE \"passengerid\" = ? AND password = ?");
			prepStatement.setString(1, userID);
			prepStatement.setString(2, pwd);
			resultSet = prepStatement.executeQuery();

			if (resultSet.next()) {
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			return false;

		}

	}

	public String registerUser(Registration registration) {
		String passengerID = createPassengerID(registration);
		try {
			prepStatement = connection
					.prepareStatement("INSERT INTO \"passenger\" VALUES (?,?,? ,?,?,?)");
			prepStatement.setString(1, registration.getFirstName());
			prepStatement.setString(2, registration.getLastName());
			try {
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				prepStatement.setDate(3,
						new Date(format.parse(registration.getBirthDate())
								.getTime()));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				return "Registration Failed: " + e.getMessage();
			}
			prepStatement.setString(4, registration.getAddress());
			prepStatement.setString(5, registration.getNationality());
			prepStatement.setString(6, passengerID);

			prepStatement.executeUpdate();

			prepStatement = connection
					.prepareStatement("INSERT INTO \"users\" VALUES (?,?,?) ");
			prepStatement.setString(1, passengerID);
			prepStatement.setString(2, registration.getPwd());
			prepStatement.setBoolean(3, false);
			prepStatement.executeUpdate();

		} catch (SQLException e) {
			return "Registration Failed: " + e.getMessage();

		}
		return "Registration Successfull! Your username is: " + passengerID;
	}

	public String createPassengerID(Registration registration) {
		String passengerID = registration.getFirstName().substring(0, 1)
				+ registration.getLastName().substring(0, 1)
				+ registration.getBirthDate().substring(2, 4)
				+ registration.getNationality();
		String passengerHash = Integer.toString(passengerID.hashCode());
		if (passengerHash.charAt(0) == '-') {
			passengerHash = passengerHash.substring(1);
		}
		if (passengerHash.length() > 4)
			passengerHash = passengerHash.substring(0, 4);
		passengerID = passengerID + passengerHash;

		return passengerID;
	}

	/**
	 * Writes a new booked trip into the database.
	 */
	public void writeInDatabase(String insurance, int luggage,
			String passenger, int tripIDInRow) {
		String newBookingQuery = "INSERT INTO booking VALUES ('" + insurance
				+ "', " + luggage + ", '" + passenger + "', '"
				+ tripIDs.get(tripIDInRow) + "');";
		Statement statementWritePerson;

		try {
			statementWritePerson = connection.createStatement();
			statementWritePerson.executeUpdate(newBookingQuery);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
